using Dapper;
using Lzfy_His_Service.Models;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Data;

namespace Lzfy_His_Service.Repositories
{
    /// <summary>
    /// 电脑软件信息数据访问层
    /// </summary>
    public interface IPcSoftwareRepository
    {
        /// <summary>
        /// 根据MAC地址查询软件信息
        /// </summary>
        /// <param name="macAddress">MAC地址</param>
        /// <returns>软件信息响应</returns>
        Task<PcSoftwareInfoResponse> GetSoftwareByMacAddressAsync(string macAddress);

        /// <summary>
        /// 根据MAC地址和条件查询软件信息（支持分页和筛选）
        /// </summary>
        /// <param name="request">查询请求</param>
        /// <returns>分页的软件信息响应</returns>
        Task<PagedResult<PcSoftwareInfo>> GetSoftwareByConditionsAsync(PcSoftwareQueryRequest request);

        /// <summary>
        /// 获取所有MAC地址列表
        /// </summary>
        /// <returns>MAC地址列表</returns>
        Task<List<string>> GetAllMacAddressesAsync();
    }

    /// <summary>
    /// 电脑软件信息数据访问层实现
    /// </summary>
    public class PcSoftwareRepository : IPcSoftwareRepository
    {
        private readonly IConfiguration _configuration;
        private readonly string _connectionString;

        public PcSoftwareRepository(IConfiguration configuration)
        {
            _configuration = configuration;
            _connectionString = _configuration.GetConnectionString("DefaultConnection") 
                ?? throw new InvalidOperationException("数据库连接字符串未配置");
        }

        /// <summary>
        /// 根据MAC地址查询软件信息
        /// </summary>
        /// <param name="macAddress">MAC地址</param>
        /// <returns>软件信息响应</returns>
        public async Task<PcSoftwareInfoResponse> GetSoftwareByMacAddressAsync(string macAddress)
        {
            const string sql = @"
                SELECT 
                    id as Id,
                    input_date as InputDate,
                    Name,
                    Version,
                    Publisher,
                    InstallDate,
                    InstallLocation,
                    Source,
                    MACAddress
                FROM Lzfy_PcSoft_Detail 
                WHERE MACAddress = @MACAddress
                ORDER BY input_date DESC, Name ASC";

            using var connection = new SqlConnection(_connectionString);
            var softwareList = await connection.QueryAsync<PcSoftwareInfo>(sql, new { MACAddress = macAddress });
            
            var result = new PcSoftwareInfoResponse
            {
                MACAddress = macAddress,
                SoftwareList = softwareList.ToList(),
                TotalCount = softwareList.Count(),
                LastUpdateTime = softwareList.FirstOrDefault()?.InputDate
            };

            return result;
        }

        /// <summary>
        /// 根据MAC地址和条件查询软件信息（支持分页和筛选）
        /// </summary>
        /// <param name="request">查询请求</param>
        /// <returns>分页的软件信息响应</returns>
        public async Task<PagedResult<PcSoftwareInfo>> GetSoftwareByConditionsAsync(PcSoftwareQueryRequest request)
        {
            var whereConditions = new List<string> { "MACAddress = @MACAddress" };
            var parameters = new DynamicParameters();
            parameters.Add("@MACAddress", request.MACAddress);

            // 添加软件名称模糊查询条件
            if (!string.IsNullOrWhiteSpace(request.SoftwareName))
            {
                whereConditions.Add("Name LIKE @SoftwareName");
                parameters.Add("@SoftwareName", $"%{request.SoftwareName}%");
            }

            // 发布商筛选功能已移除，如需要可通过软件名称模糊查询

            var whereClause = string.Join(" AND ", whereConditions);

            // 查询总数
            var countSql = $"SELECT COUNT(*) FROM Lzfy_PcSoft_Detail WHERE {whereClause}";

            // 查询数据
            var dataSql = $@"
                SELECT 
                    id as Id,
                    input_date as InputDate,
                    Name,
                    Version,
                    Publisher,
                    InstallDate,
                    InstallLocation,
                    Source,
                    MACAddress
                FROM Lzfy_PcSoft_Detail 
                WHERE {whereClause}
                ORDER BY input_date DESC, Name ASC";

            // 添加分页
            if (request.PageIndex.HasValue && request.PageSize.HasValue)
            {
                var offset = (request.PageIndex.Value - 1) * request.PageSize.Value;
                dataSql += $" OFFSET {offset} ROWS FETCH NEXT {request.PageSize.Value} ROWS ONLY";
            }

            using var connection = new SqlConnection(_connectionString);
            
            var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);
            var data = await connection.QueryAsync<PcSoftwareInfo>(dataSql, parameters);

            return new PagedResult<PcSoftwareInfo>
            {
                Items = data.ToList(),
                TotalCount = totalCount,
                PageSize = request.PageSize ?? totalCount,
                PageNumber = request.PageIndex ?? 1
            };
        }

        /// <summary>
        /// 获取所有MAC地址列表
        /// </summary>
        /// <returns>MAC地址列表</returns>
        public async Task<List<string>> GetAllMacAddressesAsync()
        {
            const string sql = @"
                SELECT DISTINCT MACAddress 
                FROM Lzfy_PcSoft_Detail 
                WHERE MACAddress IS NOT NULL AND MACAddress <> ''
                ORDER BY MACAddress";

            using var connection = new SqlConnection(_connectionString);
            var macAddresses = await connection.QueryAsync<string>(sql);
            
            return macAddresses.ToList();
        }
    }
}